package main.reports;

import main.MainApp;
import main.SQL.MsSQLConnect;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class CheckSmetReport extends Thread{
    private String pathXLSFile;
    private Connection connSQL;
    private HashMap<String, SmetReport> smetMap;
    private HashMap<String, EpsReport> epsMap;
    private HashMap<String, SmrReport> smrMap;

    private HashMap<String, CellStyle> styles;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private int rownum, curReport;


    private HashMap <String, BigDecimal> coeffs  = new HashMap<>();
    private HashMap <String, BigDecimal> facts  = new HashMap<>();

    public CheckSmetReport() {
    }

    //region run() - вызов FileChooser
    public void run() {
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Выберите путь для сохранения файла");
        fileChooser.setApproveButtonText("Сохранить");
        File fle = new File("Отчет по привязке смет - " + MainApp.curProject.getShortName().replace("/", "-").replace("\\", "-"));
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
           generateXLS();
        }
    }
    //endregion

    //region generateXLS() - Запуск генерации отчета в Excel, вызов из run()
    private void generateXLS() {
        BusyDialog busyDlg = new BusyDialog("Выгрузка отчета по привязке смет", "Выполняется SQL-запрос");
        busyDlg.start();

        connSQL = MsSQLConnect.ConnectDB("Factage");
        fillMaps();
        createReport();

        busyDlg.setDescription("Формирую отчёт.");

        try {
            connSQL.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        busyDlg.setDescription("Отчет по привязке смет готов.");
        busyDlg.stopBusy();
    }
    //endregion

    //region fillMaps() - заполнение таблиц исходных данных
    private void fillMaps() {
        try {
            smetMap = new HashMap<>();
            epsMap = new HashMap<>();
            smrMap = new HashMap<>();

            //region Заполнение таблицы и дерева EPS и смет
            CallableStatement stmt = connSQL.prepareCall("{call getCheckSmetReportEPS(?, ?)}");
            stmt.setString(1, MainApp.curProject.getDbName());
            stmt.setInt(2, MainApp.curProject.getId());
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                epsMap.put(rs.getString("eps_id") + "-" + rs.getString("importFactage"), new EpsReport(rs.getInt("eps_id"), rs.getString("eps_name"), rs.getInt("importFactage")));
                smetMap.put(rs.getString("smet_id") + "-" + rs.getString("importFactage"), new SmetReport(rs.getInt("smet_id"), rs.getString("smet_name"), rs.getString("smet_number"), rs.getInt("eps_id"), rs.getInt("importFactage")));
            }

            rs.close();
            stmt.close();

            for (SmetReport smet : smetMap.values()) {
                epsMap.get(smet.getEps()+"-"+smet.getImpFactage()).getSmetList().add(smet);
            }
            //endregion

            //region Заполнение таблицы и дерева сметных строк
            stmt = connSQL.prepareCall("{call getCheckSmetReportSmr(?, ?)}");
            stmt.setString(1, MainApp.curProject.getDbName());
            stmt.setInt(2, MainApp.curProject.getId());
            rs = stmt.executeQuery();

            while (rs.next()) {
                SmrReport smr = new SmrReport(rs.getInt("smr_id"), rs.getString("smr_name"), rs.getString("smr_num"), rs.getInt("smet_id"), rs.getInt("importFactage"), rs.getString("smr_unit"), rs.getDouble("smr_vol"));
                if (!smrMap.containsKey(rs.getString("smr_id") + "-" + rs.getString("importFactage"))) {
                    smrMap.put(rs.getString("smr_id") + "-" + rs.getString("importFactage"), smr);
                    smetMap.get(smr.getSmet()+"-"+smr.getImpFactage()).getSmrList().add(smr);
                }
            }

            rs.close();
            stmt.close();

            //endregion

            //region Добавление строк работ к сметным строкам
            stmt = connSQL.prepareCall("{call getCheckSmetReportLink(?, ?)}");
            stmt.setString(1, MainApp.curProject.getDbName());
            stmt.setInt(2, MainApp.curProject.getId());
            rs = stmt.executeQuery();

            this.coeffs  = new HashMap<>();
            this.facts  = new HashMap<>();

            while (rs.next()) {
                String smrId = rs.getString("smr_id")+"-"+rs.getString("importFactage");

                LinkReport link = new LinkReport(rs.getString("task_name"), rs.getString("task_code"), rs.getInt("smr_id"), rs.getInt("importFactage"), rs.getDouble("link_factor"), rs.getDouble("link_full_factor"), rs.getDouble("smr_vol"),rs.getDouble("assVol"));
                smrMap.get(smrId).getLinkList().add(link);

                this.coeffs.put(smrId, (coeffs.containsKey(smrId) ? coeffs.get(smrId) : new BigDecimal(0.0)).add(new BigDecimal(rs.getDouble("WORK_COEFF")).setScale(6, RoundingMode.HALF_UP)));

                if (this.coeffs.get(smrId).compareTo(new BigDecimal(0.999999)) == 1 ) {
                    link.setAssVolume(smrMap.get(smrId).getVolume().subtract((smrMap.get(smrId).getAssVolume())));
                    link.setFact(link.getFactFull().subtract(facts.containsKey(smrId) ? facts.get(smrId) : new BigDecimal(0.0)));
                    smrMap.get(smrId).setAssVolume(smrMap.get(smrId).getAssVolume().add(link.getAssVolume()));
                } else {
                    smrMap.get(smrId).setAssVolume(smrMap.get(smrId).getAssVolume().add(new BigDecimal(rs.getDouble("assVol")).setScale(6, RoundingMode.HALF_UP)));
                    this.facts.put(smrId, (facts.containsKey(smrId) ? facts.get(smrId) : new BigDecimal(0.0)).add(new BigDecimal(rs.getDouble("link_factor")).setScale(6, RoundingMode.HALF_UP)));
                }
            }
            //endregion

            rs.close();
            stmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //endregion

    //region Создание книги EXCEL, листа, запуск генерации отчёта и запись получившийся книги
    private void createReport() {
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("Лист1");
        rownum = 0;

        sheet.setRowSumsBelow(false);

        sheet.setColumnWidth(0, 256*37);
        sheet.setColumnWidth(1, 256*80);
        sheet.setColumnWidth(2, 256*20);
        sheet.setColumnWidth(3, 256*11);
        sheet.setColumnWidth(4, 256*11);
        sheet.setColumnWidth(5, 256*11);

        createStyles();
        addTitle();

        getEPSRows();
        saveXLSFile();

    }
    //endregion

    private void saveXLSFile() {
        FileOutputStream out;
        try {
            out = new FileOutputStream(new File(pathXLSFile + (pathXLSFile.contains(".xls") ? "" : ".xls")));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    private void getEPSRows() {

        for (EpsReport eps : epsMap.values()) {
            Row row = sheet.createRow(rownum++);
            Cell cell;

            for (int col = 0; col < 6; col++) {
                cell = row.createCell(col);
                switch (col) {
                    case 0:
                        cell.setCellValue("EPS");
                        break;
                    case 1:
                        cell.setCellValue(eps.getName());
                        break;
                    default:
                        cell.setCellValue("");
                        break;
                }
                cell.setCellStyle(styles.get(col == 1 ? "wbs0_left" : "wbs0"));
            }


            for (SmetReport smet : eps.getSmetList()) {
                getSmetRows(smet);
            }
        }
    }

    private void getSmetRows(SmetReport smet) {
        Row row = sheet.createRow(rownum++);
        Cell cell;

        for (int col = 0; col < 6; col++) {
            cell = row.createCell(col);
            switch (col) {
                case 0:
                    cell.setCellValue(smet.getNumber());
                    break;
                case 1:
                    cell.setCellValue("Смета: " + smet.getName());
                    break;
                default:
                    cell.setCellValue("");
                    break;
            }
            cell.setCellStyle(styles.get(col == 1 ? "wbs1_left" : "wbs1"));
        }

        if (smet.getSmrList().size() < 1 ) return;

        int groupStart = rownum;
        for (SmrReport smr : smet.getSmrList()) {
            getSmrRows(smr);
        }
        sheet.groupRow(groupStart, rownum);
    }

    private void getSmrRows(SmrReport smr) {
        Row row = sheet.createRow(rownum++);
        Cell cell;

        for (int col = 0; col < 6; col++) {
            cell = row.createCell(col);
            switch (col) {
                case 0:
                    cell.setCellValue(smr.getNumber());
                    break;
                case 1:
                    cell.setCellValue(smr.getName());
                    break;
                case 2:
                    cell.setCellValue(smr.getUnit());
                    break;
                case 3:
                    cell.setCellValue(smr.getVolume().doubleValue());
                    break;
                case 4:
                    cell.setCellValue(smr.getAssVolume().doubleValue());
                    break;
                case 5:
                    cell.setCellValue(smr.getVolume().doubleValue() == 0 ? 0.00 : smr.getAssVolume().divide(smr.getVolume(), 6, RoundingMode.HALF_UP).doubleValue());
                    break;
            }
            if (col == 1) {
                cell.setCellStyle(styles.get("task_left"));
            } else if (col == 0) {
                cell.setCellStyle(styles.get("task_right"));
            } else if (col == 2) {
                cell.setCellStyle(styles.get("task_center"));
            } else if (col == 5) {
                cell.setCellStyle(styles.get("task_percent"));
                if (smr.getVolume().doubleValue() == 0 ? true : smr.getAssVolume().divide(smr.getVolume(), 6, RoundingMode.HALF_UP).compareTo(new BigDecimal(1)) == 1) {
                    cell.setCellStyle(styles.get("task_percent_wrong"));
                } else if (smr.getAssVolume().divide(smr.getVolume(), 6, RoundingMode.HALF_UP).compareTo(new BigDecimal(1).setScale(6)) == -1) {
                    cell.setCellStyle(styles.get("task_percent_not_full"));
                }
            } else {
                cell.setCellStyle(styles.get("task_double"));
            }

        }
        int groupStart = rownum;
        for (LinkReport link : smr.getLinkList()) {
            getLinkRows(link);
        }
        sheet.groupRow(groupStart, rownum);
    }

    private void getLinkRows(LinkReport link) {
        Row row = sheet.createRow(rownum++);
        Cell cell;

        for (int col = 0; col < 6; col++) {
            cell = row.createCell(col);
            switch (col) {
                case 0:
                    cell.setCellValue(link.getCode());
                    break;
                case 1:
                    cell.setCellValue(link.getName());
                    break;
                case 2:
                    cell.setCellValue("");
                    break;
                case 3:
                    cell.setCellValue("");
                    break;
                case 4:
                    cell.setCellValue(link.getAssVolume().doubleValue());
                    break;
                case 5:
                    cell.setCellValue(link.getFact().doubleValue());
                    break;

            }
            if (col == 0) {
                cell.setCellStyle(styles.get("sm_left_grey"));
            } else if (col == 1) {
                cell.setCellStyle(styles.get("sm_left"));
            } else if (col == 4) {
                cell.setCellStyle(styles.get("sm_double"));
            } else if (col == 5) {
                cell.setCellStyle(styles.get("sm_percent"));
            } else {
                cell.setCellStyle(styles.get("sm_double"));
            }

        }
    }

    private void createStyles(){
        styles = new HashMap<>();

        CellStyle style;

        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short)57, (byte)217, (byte)217, (byte)217);

        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short)10);
        mainFont.setFontName("Calibri");

        HSSFFont boldFont = wb.createFont();
        boldFont.setFontHeightInPoints((short)10);
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldFont.setFontName("Calibri");

        HSSFFont whiteFont = wb.createFont();
        whiteFont.setFontHeightInPoints((short)10);
        whiteFont.setColor(IndexedColors.WHITE.getIndex());
        whiteFont.setFontName("Calibri");

        HSSFFont whiteBoldFont = wb.createFont();
        whiteBoldFont.setFontHeightInPoints((short) 10);
        whiteBoldFont.setColor(IndexedColors.WHITE.getIndex());
        whiteBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        whiteBoldFont.setFontName("Calibri");

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_borders", style);

        palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        style.setWrapText(true);
        styles.put("root", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        style.setWrapText(true);
        styles.put("wbs0", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs1", style);

       palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        styles.put("root_left", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        styles.put("wbs0_left", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        styles.put("wbs1_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_right", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        HSSFCreationHelper creationHelper = wb.getCreationHelper();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.000000"));
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_double", style);


        palette.setColorAtIndex((short)60, (byte)188, (byte)255, (byte)188);
        palette.setColorAtIndex((short)61, (byte)255, (byte)200, (byte)189);
        palette.setColorAtIndex((short)62, (byte)244, (byte)255, (byte)189);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
        style.setFont(boldFont);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("task_percent", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
        style.setFont(boldFont);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("task_percent_wrong", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
        style.setFont(boldFont);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(62).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("task_percent_not_full", style);


        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.000000"));
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_double", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("0.00%"));
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_percent", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left_grey", style);

    }

    //region Description
    private void addTitle() {
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(45.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellValue("№ / ID элемента");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(1);
        cell.setCellValue("Наименование элемента");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(2);
        cell.setCellValue("Единица измерения сметной строки");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(3);
        cell.setCellValue("ФО сметной строки");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(4);
        cell.setCellValue("ФО распределенный");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(5);
        cell.setCellValue("% распределения");
        cell.setCellStyle(styles.get("topTitle_borders"));

        row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellStyle(styles.get("root"));

        cell = row.createCell(1);
        cell.setCellValue(MainApp.curProject.getName().replace(" [ROOT]", ""));
        cell.setCellStyle(styles.get("root_left"));

        cell = row.createCell(2);
        cell.setCellStyle(styles.get("root"));

        cell = row.createCell(3);
        cell.setCellStyle(styles.get("root"));

        cell = row.createCell(4);
        cell.setCellStyle(styles.get("root"));

        cell = row.createCell(5);
        cell.setCellStyle(styles.get("root"));

    }
    //endregion

}

class SmetReport {

    private int id, eps, impFactage;
    private String name, number;
    private ArrayList<SmrReport> smrList;

    public SmetReport(int id, String name, String number, int eps, int impFactage) {
        this.id = id;
        this.name = name;
        this.number = number;
        this.eps = eps;
        this.impFactage = impFactage;
        this.smrList = new ArrayList<>();
    }


    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getNumber() {
        return number;
    }

    public int getEps() {
        return eps;
    }

    public int getImpFactage() {
        return impFactage;
    }

    public ArrayList<SmrReport> getSmrList() {
        return smrList;
    }
}

class EpsReport {

    private int id, impFactage;
    private String name;
    private ArrayList<SmetReport> smetList;

    public EpsReport(int id, String name, int impFactage) {
        this.id = id;
        this.name = name;
        this.impFactage = impFactage;
        this.smetList = new ArrayList<>();
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getImpFactage() {
        return impFactage;
    }

    public ArrayList<SmetReport> getSmetList() {
        return smetList;
    }
}

class SmrReport {

    private int id, smet, impFactage;
    private String name, number, unit;
    private BigDecimal volume, assVolume, percent;
    private ArrayList<LinkReport> linkList;

    public SmrReport(int id, String name, String number, int smet, int impFactage, String unit, Double volume) {
        this.id = id;
        this.name = name;
        this.number = number;
        this.smet = smet;
        this.impFactage = impFactage;
        this.unit = unit;
        this.volume = new BigDecimal(volume).setScale(6, RoundingMode.HALF_UP);
        this.linkList = new ArrayList<>();
        this.assVolume = new BigDecimal(0).setScale(6);
    }


    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getNumber() {
        return number;
    }

    public int getImpFactage() {
        return impFactage;
    }

    public int getSmet() {
        return smet;
    }

    public String getUnit() {
        return unit;
    }

    public BigDecimal getVolume() {
        return volume;
    }

    public BigDecimal getPercent() {
        return percent;
    }

    public void setPercent(BigDecimal percent) {
        this.percent = percent;
    }

    public BigDecimal getAssVolume() {
        return assVolume;
    }

    public void setAssVolume(BigDecimal assVolume) {
        this.assVolume = assVolume;
    }

    public ArrayList<LinkReport> getLinkList() {
        return linkList;
    }
}

class LinkReport {

    private int smr, impFactage;
    private String code, name;
    private BigDecimal volume, assVolume, fact, factFull;

    public LinkReport(String name, String code, int smr, int impFactage, Double fact, Double factFull, Double volume, Double assVolume) {
        this.name = name;
        this.code = code;
        this.smr = smr;
        this.impFactage = impFactage;
        this.fact = new BigDecimal(fact).setScale(6, RoundingMode.HALF_UP);
        this.factFull = new BigDecimal(factFull).setScale(6, RoundingMode.HALF_UP);
        this.volume = new BigDecimal(volume).setScale(6, RoundingMode.HALF_UP);
        this.assVolume = new BigDecimal(assVolume).setScale(6, RoundingMode.HALF_UP);
    }


    public String getName() {
        return name;
    }

    public int getImpFactage() {
        return impFactage;
    }

    public BigDecimal getVolume() {
        return volume;
    }

    public String getCode() {
        return code;
    }

    public int getSmr() {
        return smr;
    }

    public BigDecimal getAssVolume() {
        return assVolume;
    }

    public void setAssVolume(BigDecimal assVolume) {
        this.assVolume = assVolume;
    }

    public BigDecimal getFact() {
        return fact;
    }

    public void setFact(BigDecimal fact) {
        this.fact = fact;
    }

    public BigDecimal getFactFull() {
        return factFull;
    }
}
